/**
 * org.vz.product.collectMaintain.service.impl
 * @since : jdk 1.8
 */
package org.vz.product.collect.maintain.service.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.beetl.sql.core.engine.PageQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.vz.product.collect.maintain.constant.DataStatus;
import org.vz.product.collect.maintain.dao.AcquisitionDao;
import org.vz.product.collect.maintain.dao.AcquisitionQueueDao;
import org.vz.product.collect.maintain.dao.AcquisitionTaskDao;
import org.vz.product.collect.maintain.dao.KettleTransDao;
import org.vz.product.collect.maintain.dao.TransLogDao;
import org.vz.product.collect.maintain.dao.TransMapRowsDao;
import org.vz.product.collect.maintain.dao.TransRecordBlackDao;
import org.vz.product.collect.maintain.domain.Acquisition;
import org.vz.product.collect.maintain.domain.AcquisitionQueue;
import org.vz.product.collect.maintain.domain.AcquisitionTask;
import org.vz.product.collect.maintain.domain.KettleTransformation;
import org.vz.product.collect.maintain.domain.TransLog;
import org.vz.product.collect.maintain.domain.TransMapRows;
import org.vz.product.collect.maintain.domain.TransRecordBlack;
import org.vz.product.collect.maintain.domain.WorkStatistics;
import org.vz.product.collect.maintain.domain.entity.StatResult;
import org.vz.product.collect.maintain.domain.entity.Transformation;
import org.vz.product.collect.maintain.service.TransLogService;
import org.vz.product.collect.maintain.util.PageUtil;
import org.vz.product.collect.maintain.util.SortList;
import org.vz.product.collect.maintain.util.StringUtil;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * @author      lanxuyu
 * @CreateTime  2017年09月07日 15:53:24
 * @version     1.0.0
 * @description 日志service层
 */
@Service
public class TransLogServiceImpl implements TransLogService{
	@Autowired
	TransLogDao tlDao;
	@Autowired
	DruidDataSource dataSource;
	@Autowired
	AcquisitionDao acDao;
	@Autowired
	AcquisitionTaskDao acTaskDao;
	@Autowired
	KettleTransDao ktrDao;
	@Autowired
	AcquisitionQueueDao aqDao;
	@Autowired
	TransRecordBlackDao trbDao;
	@Autowired
	TransMapRowsDao tmrDao;
	
	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#pageList(org.beetl.sql.core.engine.PageQuery)
	 */
	@Override
	public PageQuery<TransLog> batchList(PageQuery<TransLog> query) {
		PageQuery<TransLog> queryResult = tlDao.getSQLManager().pageQuery("translog.pageQuery", TransLog.class, query);
		
		return queryResult;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getBatchDetail(long, long, org.vz.product.collect.maintain.domain.TransMapRows)
	 */
	@Override
	public PageUtil getBatchDetail(Integer pageNumber, Integer pageSize,List<TransMapRows> tmrs,String startTime,String endTime){
		PageUtil page=new PageUtil();
		page.setPageNumber(pageNumber);
		page.setPageSize(pageSize);
		String sql="";
		Connection connection=null;;
		try {
			
		
			List<Map<Integer,String>> data=new ArrayList<Map<Integer,String>>();
			List<String> colName=new ArrayList<>();
			int count=0;
			for (TransMapRows transMapRow : tmrs) {
				String targetTableName=transMapRow.getTargetTableName();
				String idBatchTarget=transMapRow.getIdBatchTarget();
				if(!startTime.equals("")&&!endTime.equals("")){
					sql="SELECT *,ROW_NUMBER() OVER(ORDER BY [CreateTime] DESC) AS num FROM "+targetTableName+" with(nolock) WHERE ID=? AND CreateTime BETWEEN '"+startTime+"' AND '"+endTime+" ORDER BY num OFFSET "+ page.getStartRow()+" ROWS FETCH NEXT "+pageSize+" ROWS ONLY";
				}else{
					sql="SELECT *,ROW_NUMBER() OVER(ORDER BY [CreateTime] DESC) AS num FROM "+targetTableName+" with(nolock) WHERE ID=? ORDER BY num OFFSET "+ page.getStartRow()+" ROWS FETCH NEXT "+pageSize+" ROWS ONLY";
				}
				connection = dataSource.getConnection();
				PreparedStatement ps = connection.prepareStatement(sql);
				ps.setString(1,idBatchTarget );
				ResultSet rs = ps.executeQuery();
				
			    Map<Integer,String> rows=new LinkedHashMap<>();
				
				ResultSetMetaData metaData = rs.getMetaData(); 
				int columnCount = metaData.getColumnCount();
				if(colName.size()==0){
					for (int i = 1; i <columnCount; i++) {
						colName.add(metaData.getColumnName(i));
		               
					}
				}
				while(rs.next()){				
					 for(int i = 1; i < columnCount; i++) {//遍历列  
						 if(rs.getString(i)==null||"".equals(rs.getString(i))){
							 rows.put(i, "null");
						 }else{
						 	rows.put(i, rs.getString(i));
						 }
		                    
		                }  
					 
					 	data.add(rows);
				}
				
				 sql = "select count(*) from "+targetTableName+" with(nolock) where id=?";
				 PreparedStatement pscount=connection.prepareStatement(sql);
				 pscount.setString(1, idBatchTarget);
			     ResultSet rscount = pscount.executeQuery();  
			     rscount.next();  
			     count = count+rscount.getInt(1);
			}
		
		    page.setData(data);
		    page.setColName(colName);
		    page.setRowCount(count);
		
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(connection!=null){
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
			
		return page;
	}
	
	/* (non-Javadoc)
	 * @see org.vz.product.data.collect.service.AcquisitionService#pageList(int)
	 */
	@Override
	public Map<String, Object> pageList(PageQuery<Acquisition> query,String queryname) {
		Map<String,String> param=new HashMap<>();
		
		param.put("queryname", queryname);
		query.setParas(param);
		PageQuery<Acquisition> queryResult = acDao.getSQLManager().pageQuery("acquisition.pageQuery", Acquisition.class, query);
		List<Acquisition> acquisitions = queryResult.getList();
		List<WorkStatistics> works=new ArrayList<>();
		AcquisitionTask acquisitionTask = new AcquisitionTask();
		
		for (Acquisition acquisition : acquisitions) {
			acquisitionTask.setAcquisitioncode(acquisition.getCode());		
			List<AcquisitionTask> acTasks = acTaskDao.getSQLManager().select("acquisition.getActask", AcquisitionTask.class, acquisitionTask);	
			for (AcquisitionTask acTask : acTasks) {
				
				KettleTransformation ktrTrans = ktrDao.getSQLManager().select("transformation.selectById", KettleTransformation.class, acTask).get(0);
				Map<String,Object> params=new HashMap<>();
				params.put("acId",acquisition.getCode());
				params.put("status",DataStatus.EXECUTED_STATUS);
				params.put("ktrId",ktrTrans.getIdTransformation());
				List<WorkStatistics> wss = tlDao.getSQLManager().select("queryResult.workStatistics",WorkStatistics.class,params);
				WorkStatistics workStatistics = workStatisticsIsNull(wss);
				workStatistics.setAcCode(acquisition.getCode());
				workStatistics.setAcName(acquisition.getName());
				workStatistics.setKtrId(ktrTrans.getIdTransformation());
				workStatistics.setKtrName(ktrTrans.getName());
				works.add(workStatistics);
			}			
				
			
		}
		Map<String,Object> wsList=new HashMap<>();
		wsList.put("rows", works);		
		wsList.put("pageNo",queryResult.getPageNumber());
		wsList.put("pageSize", queryResult.getPageSize());
		wsList.put("pageCounts", queryResult.getTotalPage());
		wsList.put("total", queryResult.getTotalRow());
		return wsList;
		
	}





	
	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.AcquisitionService#listAll(java.lang.String, java.lang.String)
	 */
	@Override
	public Map<String, Object> statisticsDetail(PageQuery<Acquisition> query) {
		
		PageQuery<Acquisition> queryResult = acDao.getSQLManager().pageQuery("acquisition.statisticsDetail", Acquisition.class,query);
		List<Acquisition> acquisitions = queryResult.getList();
		List<WorkStatistics> works=new ArrayList<>();
		AcquisitionTask acquisitionTask = new AcquisitionTask();
		Map<String, Object> data=new HashMap<>();
		Map<String, Object> page=new HashMap<>();
		for (Acquisition acquisition : acquisitions) {
			acquisitionTask.setAcquisitioncode(acquisition.getCode());		
			List<AcquisitionTask> acTasks = acTaskDao.getSQLManager().select("acquisition.getActask", AcquisitionTask.class, acquisitionTask);
			for (AcquisitionTask acTask : acTasks) {
				
				KettleTransformation ktrTrans = ktrDao.getSQLManager().select("transformation.selectById", KettleTransformation.class, acTask).get(0);
				Map<String,Object> params=new HashMap<>();
				params.put("acId",acquisition.getCode());
				params.put("status",DataStatus.EXECUTED_STATUS);
				params.put("ktrId",ktrTrans.getIdTransformation());
				List<WorkStatistics> wss = tlDao.getSQLManager().select("queryResult.workStatistics",WorkStatistics.class,params);
				WorkStatistics workStatistics = workStatisticsIsNull(wss);
				workStatistics.setAcCode(acquisition.getCode());
				workStatistics.setAcName(acquisition.getName());
				workStatistics.setKtrId(ktrTrans.getIdTransformation());
				workStatistics.setKtrName(ktrTrans.getName());
				works.add(workStatistics);
			}			
				
			
		}
		Map<String,Object> wsList=new HashMap<>();
		page.put("pageNo",queryResult.getPageNumber());
		page.put("pageSize", queryResult.getPageSize());
		page.put("pageCounts", queryResult.getTotalPage());
		page.put("total", queryResult.getTotalRow());
		data.put("page", page);
		data.put("works", works);
		wsList.put("data", data);
		wsList.put("status", 1);	
		wsList.put("msg", "查询成功！");	
		
		return wsList;
	}

	/**
	 * @description  
	 * @author       lanxuyu
	 * @CreateTime   2017年9月18日 下午2:00:04
	 * @param workStatistics
	 * @param wss
	 * @return
	 */
	private WorkStatistics workStatisticsIsNull(List<WorkStatistics> wss) {
		WorkStatistics workStatistics=new WorkStatistics();
		if(wss.get(0)==null){
			workStatistics.setSuccess(0);
			workStatistics.setSuccess(0);
			workStatistics.setErrors(0);
		}else{
			workStatistics=wss.get(0);
			if(workStatistics.getSuccess()==null){
				workStatistics.setSuccess(0);
			}
			if(workStatistics.getFailed()==null){
				workStatistics.setFailed(0);
			}
			if(workStatistics.getErrors()==null){
				workStatistics.setErrors(0);
			}
		}
		return workStatistics;
	}






	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.AcquisitionService#statistics(java.lang.String, java.lang.String)
	 */
	@Override
	public StatResult<Map<String, Object>> statistics(String startTime, String endTime) {
		
		Map<String,Object> params=new HashMap<>();
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		params.put("status",DataStatus.EXECUTED_STATUS);
		List<WorkStatistics> wss = tlDao.getSQLManager().select("queryResult.Statistics",WorkStatistics.class,params);
		WorkStatistics workStatistics = workStatisticsIsNull(wss);
		Map<String,Object> data=new HashMap<>();
		data.put("totalCount", workStatistics.getErrors());
		StatResult<Map<String, Object>> statResult = new StatResult<Map<String, Object>>();
		statResult.setStatus(1);
		statResult.setMsg("查询成功！");
		statResult.setData(data);
		return statResult;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getMsgSuccess(org.vz.product.collect.maintain.domain.Acquisition)
	 */
	@Override
	public Map<String, Object> getMsgSuccess(Acquisition acquisition,PageQuery<AcquisitionQueue> query, String startTime, String endTime) {
		Map<String,Object> params=new HashMap<>();
		params.put("acId",acquisition.getCode());
		params.put("status", DataStatus.EXECUTED_STATUS);
		
		PageQuery<AcquisitionQueue> queryResult=null;
		if("".equals(startTime)&&"".equals(endTime)){
			query.setParas(params);
			queryResult = aqDao.getSQLManager().pageQuery("queryResult.getMsgSuccess", AcquisitionQueue.class,query);
		}
		else if(!("".equals(startTime))&&("".equals(endTime))){
			endTime=StringUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss");
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = aqDao.getSQLManager().pageQuery("queryResult.searchMsgSuccess", AcquisitionQueue.class,query);
		}
		else if(!("".equals(startTime))&&!("".equals(endTime))){
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = aqDao.getSQLManager().pageQuery("queryResult.searchMsgSuccess", AcquisitionQueue.class,query);
		}
		Map<String,Object> result=new HashMap<>();
		result.put("rows", queryResult.getList());		
		result.put("pageNo",queryResult.getPageNumber());
		result.put("pageSize", queryResult.getPageSize());
		result.put("pageCounts", queryResult.getTotalPage());
		result.put("total", queryResult.getTotalRow());
		return result;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getMsgFailed(org.vz.product.collect.maintain.domain.TransRecordBlack)
	 */
	@Override
	public Map<String, Object> getMsgFailed(TransRecordBlack transRecordBlack,PageQuery<TransRecordBlack> query, String startTime, String endTime) {
		Map<String,Object> params=new HashMap<>();
		params.put("ktrId",transRecordBlack.getKtrId());
		PageQuery<TransRecordBlack> queryResult=null;
		if("".equals(startTime)&&"".equals(endTime)){
			query.setParas(params);
			queryResult = aqDao.getSQLManager().pageQuery("queryResult.getMsgFailed", TransRecordBlack.class,query);
		}
		else if(!("".equals(startTime))&&("".equals(endTime))){
			endTime=StringUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss");
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = trbDao.getSQLManager().pageQuery("queryResult.searchMsgFailed", TransRecordBlack.class,query);
		}
		else if(!("".equals(startTime))&&!("".equals(endTime))){
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = trbDao.getSQLManager().pageQuery("queryResult.searchMsgFailed", TransRecordBlack.class,query);
		}
		Map<String,Object> result=new HashMap<>();
		result.put("rows", queryResult.getList());		
		result.put("pageNo",queryResult.getPageNumber());
		result.put("pageSize", queryResult.getPageSize());
		result.put("pageCounts", queryResult.getTotalPage());
		result.put("total", queryResult.getTotalRow());
		return result;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getPlatform(org.vz.product.collect.maintain.domain.TransMapRows)
	 */
	@Override
	public Map<String, Object> getPlatform(TransMapRows transMapRows,PageQuery<TransMapRows> query, String startTime, String endTime) {
		Map<String,Object> params=new HashMap<>();
		params.put("ktrId",transMapRows.getKtrId());
		PageQuery<TransMapRows> queryResult=null;
		if("".equals(startTime)&&"".equals(endTime)){
			query.setParas(params);
			queryResult = tmrDao.getSQLManager().pageQuery("queryResult.getPlatform", TransMapRows.class,query);
		}
		else if(!("".equals(startTime))&&("".equals(endTime))){
			endTime=StringUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss");
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = tmrDao.getSQLManager().pageQuery("queryResult.searchPlatform", TransMapRows.class,query);
		}
		else if(!("".equals(startTime))&&!("".equals(endTime))){
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = tmrDao.getSQLManager().pageQuery("queryResult.searchPlatform", TransMapRows.class,query);
		}
		Map<String,Object> result=new HashMap<>();
		result.put("rows", queryResult.getList());		
		result.put("pageNo",queryResult.getPageNumber());
		result.put("pageSize", queryResult.getPageSize());
		result.put("pageCounts", queryResult.getTotalPage());
		result.put("total", queryResult.getTotalRow());
		return result;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#allPlatform(org.beetl.sql.core.engine.PageQuery, java.lang.String, java.lang.String)
	 */
	@Override
	public StatResult<Map<String,Object>> allPlatform(PageQuery<TransMapRows> query, String startTime, String endTime) {
		Map<String,Object> params=new HashMap<>();
		PageQuery<TransMapRows> queryResult=null;
		if(!("".equals(startTime))&&("".equals(endTime))){
			endTime=StringUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss");
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = tmrDao.getSQLManager().pageQuery("queryResult.allPlatform", TransMapRows.class,query);
		}
		else if(!("".equals(startTime))&&!("".equals(endTime))){
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			query.setParas(params);
			queryResult = tmrDao.getSQLManager().pageQuery("queryResult.allPlatform", TransMapRows.class,query);
		}
		Map<String,Object> data=new HashMap<>();
		data.put("rows", queryResult.getList());		
		data.put("pageNo",queryResult.getPageNumber());
		data.put("pageSize", queryResult.getPageSize());
		data.put("pageCounts", queryResult.getTotalPage());
		data.put("total", queryResult.getTotalRow());
		StatResult<Map<String,Object>> result = new StatResult<Map<String,Object>>();
		
		result.setStatus(1);
		result.setMsg("查询成功！");
		result.setData(data);
		return result;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#allMsgFailed(org.beetl.sql.core.engine.PageQuery, java.lang.String, java.lang.String)
	 */
	@Override
	public StatResult<List<TransRecordBlack>> allMsgFailed(String startTime,
			String endTime) {
		Map<String,Object> params=new HashMap<>();
		List<TransRecordBlack> errors=null;
		if(!("".equals(startTime))&&("".equals(endTime))){
			endTime=StringUtil.dateToString(new Date(),"yyyy-MM-dd HH:mm:ss");
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			
			errors = trbDao.getSQLManager().select("queryResult.allMsgFailed", TransRecordBlack.class,params);
		}
		else if(!("".equals(startTime))&&!("".equals(endTime))){
			params.put("startTime", startTime);
			params.put("endTime", endTime);
			errors = trbDao.getSQLManager().select("queryResult.allMsgFailed", TransRecordBlack.class,params);
		}
		Map<String,Object> data=new HashMap<>();
		
		StatResult<List<TransRecordBlack>> result = new StatResult<List<TransRecordBlack>>();
		
		result.setStatus(1);
		result.setMsg("查询成功！");
		result.setData(errors);
		return result;
	}

	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getWorkingTrans(java.lang.String)
	 */
	@Override
	public StatResult<Map<String,Object>> getWorkingTrans() {
		
		List<Transformation> transformations = ktrDao.getSQLManager().select("queryResult.searchWorkingTrans",Transformation.class);
		
		StatResult<Map<String,Object>> result = new StatResult<Map<String,Object>>();
		Map<String,Object> data=new HashMap<>();
		data.put("detail", transformations);
		data.put("totalCount", transformations.size());
		result.setStatus(1);
		result.setMsg("查询成功！");
		result.setData(data);
		return result;
	}
	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#getWaitingTrans()
	 */
	@Override
	public StatResult<Map<String,Object>> getWaitingTrans() {
List<Transformation> transformations = ktrDao.getSQLManager().select("queryResult.searchWaitingTrans",Transformation.class);
		
		StatResult<Map<String,Object>> result = new StatResult<Map<String,Object>>();
		
		Map<String,Object> data=new HashMap<>();
		data.put("detail", transformations);
		data.put("totalCount", transformations.size());
		result.setStatus(1);
		result.setMsg("查询成功！");
		result.setData(data);
		return result;
	}
	/* (non-Javadoc)
	 * @see org.vz.product.collect.maintain.service.TransLogService#statisticsAllDetail()
	 */
	@Override
	public StatResult<List<WorkStatistics>> statisticsAllDetail(String startTime, String endTime) {
		List<Acquisition> acquisitions = acDao.getSQLManager().select("acquisition.getAll", Acquisition.class);
		List<WorkStatistics> works=new ArrayList<>();
		AcquisitionTask acquisitionTask = new AcquisitionTask();
		for (Acquisition acquisition : acquisitions) {
			acquisitionTask.setAcquisitioncode(acquisition.getCode());		
			List<AcquisitionTask> acTasks = acTaskDao.getSQLManager().select("acquisition.getActask", AcquisitionTask.class, acquisitionTask);
			for (AcquisitionTask acTask : acTasks) {
				KettleTransformation ktrTrans = ktrDao.getSQLManager().select("transformation.selectById", KettleTransformation.class, acTask).get(0);
				Map<String,Object> params=new HashMap<>();
				params.put("acId",acquisition.getCode());
				params.put("status",DataStatus.EXECUTED_STATUS);
				params.put("ktrId",ktrTrans.getIdTransformation());
				params.put("startTime", startTime);
				params.put("endTime", endTime);
				List<WorkStatistics> wss = tlDao.getSQLManager().select("queryResult.workStatistics",WorkStatistics.class,params);
				WorkStatistics workStatistics = workStatisticsIsNull(wss);
				List<Integer> states = tlDao.getSQLManager().select("queryResult.getWorkState",Integer.class,params);
				if(states.size()>0){
					workStatistics.setState(1);
					
				}else{
					workStatistics.setState(0);
				}
				
				workStatistics.setAcCode(acquisition.getCode());
				workStatistics.setAcName(acquisition.getName());
				workStatistics.setKtrId(ktrTrans.getIdTransformation());
				workStatistics.setKtrName(ktrTrans.getName());
				works.add(workStatistics);
			}			
				
			
		}
		new SortList<WorkStatistics>().Sort(works, "getFailed", "desc");;
		StatResult<List<WorkStatistics>> statResult = new StatResult<>();
		statResult.setData(works);
		statResult.setMsg("查询成功！");
		statResult.setStatus(1);
		
		
		return statResult;
	}

	
	
	
	

}
	


